﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
//using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using System.Deployment.Application;

namespace EvePos
{
    public partial class FormMain : Form
    {
        SupportFn fn = new SupportFn();
        bool directorRole;
        public bool servervarchange;
        string keyid, vcode, keyidcorp, vcodecorp, corpname, towerfilter = "", groupfilter = "";
        string closed = "4", opened = "6";
        public string charname;
        long charid, corpid;

        public FormMain()
        {
            InitializeComponent();
            fn.filepath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            if (!Directory.Exists(fn.filepath))
            {   // cant run
                MessageBox.Show("Document path doesnt exist, Exiting", "Error");
                return;
            }
            else if (!Directory.Exists(fn.filepath + "\\EveXmlCache"))
            {
                Directory.CreateDirectory(fn.filepath + "\\EveXmlCache");
            }
            if (!Directory.Exists(fn.filepath + "\\EveXmlCache"))
            {   // cant run
                MessageBox.Show("Cant create " + fn.filepath + "\\EveXmlCache, Exiting", "Error");
                return;
            }
            fn.filepath += "\\EveXmlCache";
            // copy user db
            if (!File.Exists(fn.filepath + "\\EveVar.db"))
                File.Copy("EveVar.db", fn.filepath + "\\EveVar.db");
            fn.connstr = "Data Source=" + fn.filepath + "\\EveVar.db";
            if (!File.Exists(fn.filepath + "\\EveUser.db"))
                File.Copy("EveUser.db", fn.filepath + "\\EveUser.db");
            fn.connstruser = "Data Source=" + fn.filepath + "\\EveUser.db";
            // we could place an updated eve db there if we want
            fn.connstrstat = "Data Source=EveStat.db";
            if (File.Exists(fn.filepath + "\\EveStat.db"))
                fn.connstrstat = "Data Source=" + fn.filepath + "\\EveStat.db";
            // disable mouse wheel on combobox
            comboBoxChar.MouseWheel += new MouseEventHandler(comboBoxChar_MouseWheel);
            comboBoxOwner.MouseWheel += new MouseEventHandler(comboBoxOwner_MouseWheel);
            comboBoxSystem.MouseWheel += new MouseEventHandler(comboBoxSystem_MouseWheel);
            // get user into combobox
            getUserList();
            // reaction col0 header symbol font
            dataGridViewReaction.Columns[0].HeaderCell.Style.Font = new Font("Webdings", 12, GraphicsUnit.Pixel);
            dataGridViewIsk.Columns[0].HeaderCell.Style.Font = new Font("Webdings", 12, GraphicsUnit.Pixel);
            dataGridViewAmmo.Columns[0].HeaderCell.Style.Font = new Font("Webdings", 12, GraphicsUnit.Pixel);
            // use extern db?
            string str = fn.readUserSql("select int,string from vars where varName='useMySql'");
            if (str != "")
            {
                textBoxMySql.Text = fn.getSubStr(1, str);
                if (fn.getSubStr(0, str) == "1")
                {
                    checkBoxMySql.Checked = true;
                    textBoxMySql.ReadOnly = true;
                    if (textBoxMySql.Text.ToLower().Contains("data source=") && textBoxMySql.Text.ToLower().Contains("database="))
                        fn.mysqlconnstr = textBoxMySql.Text;
                    else textBoxMySql.Text = "";
                }
            }
            string swversion = fn.readUserSql("select string from vars where varName='PosToolVersion'");
            string swversion2 = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
            if (swversion == "" || swversion.Substring(0, swversion.IndexOf(".")) != swversion2.Substring(0, swversion2.IndexOf(".")))
            {   // rebuild db etc.
                foreach (FileInfo f in new DirectoryInfo(fn.filepath).GetFiles("*.xml"))
                {
                    f.Delete();
                }
                fn.createTables();
                fn.writeUserSql("replace into vars values('PosToolVersion',0,null,null,'" + Version() + "')");
            }
            labelVersion.Text = "Version: " + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
            fn.comname = System.Windows.Forms.SystemInformation.ComputerName.ToString();
        }

        private void fetchApiData()
        {
            string xmlstr, lineval;
            fn.writeDebug("--- get charinfo ---");
            fn.apiErrors = 0;
            fn.updatereallocation = false;
            if (charname != "TESTDATA")
            {
                lineval = fn.Decrypt(fn.readUserSql("select code from user where name='" + fn.strToSQLiteStr(charname) + "';"), fn.comname);
                if (lineval != "")
                {
                    keyid = fn.getSubStr(0, lineval);
                    // tag = fn.getSubStr(1, lineval);
                    vcode = fn.getSubStr(2, lineval);
                }
                else return;
            }

            charid = Convert.ToInt64(fn.readUserSql("select charID from user where name='" + fn.strToSQLiteStr(charname) + "';"));
            corpid = Convert.ToInt64(fn.readUserSql("select corpID from user where name='" + fn.strToSQLiteStr(charname) + "';"));
            if (charname != "TESTDATA")
            {
                fn.testdata = false;
                xmlstr = fn.fetchApi(fn.apiserver + "account/APIKeyInfo.xml.aspx?keyID=" + keyid + "&vCode=" + vcode);
                if (xmlstr.Contains("<error code="))
                {
                    MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api account/APIKeyInfo");
                    return;
                }
                else if (xmlstr == "")
                    MessageBox.Show("Empty return", "Api account/APIKeyInfo");
                else // vergleichen
                {
                    StringReader strread = new StringReader(xmlstr);
                    string strline;
                    while ((strline = strread.ReadLine()) != null)
                    {
                        if (strline.Contains("\"" + charname + "\""))
                        {
                            charid = Convert.ToInt64(fn.fetchXmlValue("characterID=", strline));
                            corpid = Convert.ToInt64(fn.fetchXmlValue("corporationID=", strline));
                        }
                    }

                }
                if (charid == 0 || corpid == 0)
                {
                    MessageBox.Show("Character id could not be read", "Api account/Characters");
                    return;
                }
            }
            else fn.testdata = true;
            corpname = fn.readUserSql("select name from corp where corpID=" + corpid + ";");
            if (charname != "TESTDATA")
            {
                if (corpname == "")
                {
                    MessageBox.Show("No corp API present", "Api account/Characters");
                    return;
                }

                lineval = fn.Decrypt(fn.readUserSql("select code from corp where corpID=" + corpid + ";"), fn.comname);
                if (lineval != "")
                {
                    keyidcorp = fn.getSubStr(0, lineval);
                    vcodecorp = fn.getSubStr(1, lineval);
                }
                else return;
            }
            // fetch sov
            fn.writeDebug("--- get sovereignty ---");
            fn.apiFetchSovereignity();
            // fetch player station list from api
            fn.writeDebug("--- get outpost list ---");
            xmlstr = fn.cachedApi(fn.apiserver + "eve/ConquerableStationList.xml.aspx", "Outposts");
            if (xmlstr.Contains("<error code="))
                MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api eve/ConquerableStationList");
            else if (xmlstr == "")
                MessageBox.Show("Empty return", "Api eve/ConquerableStationList");
            if (xmlstr == "" || xmlstr.Contains("<error code="))
                return;
            fn.outpostsToDb(xmlstr);

            // fetch private assets
            fn.writeDebug("--- get player assets ---");
            xmlstr = fn.cachedApi(fn.apiserver + "char/AssetList.xml.aspx?keyID=" + keyid + "&vCode=" + vcode + "&characterID=" + charid, "AssetListChar " + charid);
            fn.assetsToDb(xmlstr, false, charid, corpid);
            fn.writeUserSql("replace into vars values('AssetsTimestampPriv" + charid + "',0,null,'" + fn.fetchXmlAttrib("<cachedUntil", xmlstr) + "',null)");
            // fetch corp hangar names
            fn.writeDebug("--- ask corp sheet ---");
            xmlstr = fn.cachedApi(fn.apiserver + "corp/CorporationSheet.xml.aspx?keyID=" + keyidcorp + "&vCode=" + vcodecorp, "CorporationSheet " + corpid);
            if (xmlstr.Contains("<error code="))
                MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api CorporationSheet");
            else if (xmlstr == "")
                MessageBox.Show("Empty return", "Api CorporationSheet");
            else fn.setCorpHangars(xmlstr);
            // fetch corp assets
            fn.writeDebug("--- get corp assets ---");
            xmlstr = fn.cachedApi(fn.apiserver + "corp/AssetList.xml.aspx?keyID=" + keyidcorp + "&vCode=" + vcodecorp, "AssetListCorp " + corpid);
            if (xmlstr.Contains("<error code="))
                MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api AssetList");
            else if (xmlstr == "")
                MessageBox.Show("Empty return", "Api AssetList");
            if (xmlstr == "" || xmlstr.Contains("<error code="))
                return;
            fn.assetscacheduntil = "Next update: " + DateTime.Parse(fn.fetchXmlAttrib("<cachedUntil", xmlstr)).ToString() + " UTC  (" + DateTime.Parse(fn.fetchXmlAttrib("<cachedUntil", xmlstr)).ToLocalTime() + " Local)";
            fn.timestampcorpassets = fn.fetchXmlAttrib("<cachedUntil", xmlstr);
            fn.writeUserSql("replace into vars values('AssetsTimestamp" + corpid + "',0,null,'" + fn.timestampcorpassets + "',null)");
            // save into db
            fn.assetsToDb(xmlstr, true, charid, corpid);
            // get custom named items
            fn.writeDebug("--- get unique names ---");
            fn.apiFetchUniqueNames(true, charid, corpid, keyidcorp, vcodecorp);
            fn.apiFetchUniqueNames(false, charid, corpid, keyid, vcode);
            // fetch starbase list
            fn.writeDebug("--- get starbase list ---"); 
            if (keyidcorp != "" && vcodecorp != "")
            {
                xmlstr = fn.cachedApi(fn.apiserver + "corp/StarbaseList.xml.aspx?keyID=" + keyidcorp + "&vCode=" + vcodecorp, "StarbaseList " + corpid);
                if (xmlstr.Contains("<error code="))
                    MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api StarbaseList");
                else if (xmlstr == "")
                    MessageBox.Show("Empty return", "Api StarbaseList");
                else
                {
                    fn.starbasesToDb(xmlstr, corpid);
                }
            }
            else return;
            // determine the true location of items (must be called behind fetch names because it uses the coords of the custom items)
            fn.writeDebug("--- set real location corp assets ---"); 
            fn.setRealLocation(true, charid, corpid);
            fn.writeDebug("--- set real location char assets ---");
            fn.setRealLocation(false, charid, corpid);
            // get starbase detail lists
            fn.writeDebug("--- get pos details ---");
            fn.apiFetchPosDetail(corpid, keyidcorp, vcodecorp);
            fn.writeDebug("--- assemble tower reactions ---");
            fn.setTowerReactions(corpid);
            // remove old cached prices
            foreach (FileInfo f in new DirectoryInfo(fn.filepath).GetFiles("Isk *.xml"))
            {
                if (f.LastWriteTimeUtc.Subtract(DateTime.UtcNow).TotalMinutes < -120.0)
                    f.Delete();
            }
        }

        private void comboBoxChar_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBoxChar.Text == "")
                return;
            charname = comboBoxChar.Text;
            labelAssets.Text = fn.assetscacheduntil;
            // display
            corpid = Convert.ToInt64(fn.readUserSql("select corpID from user where name='" + fn.strToSQLiteStr(charname) + "';"));
            labelAssets.Text = "no current data";
            comboBoxOwner.Items.Clear();
            comboBoxOwner.Text = "- All -";
            comboBoxSystem.Items.Clear();
            comboBoxSystem.Text = "- All -";
            fn.timestampcorpassets = fn.readUserSql("select timestamp from vars where varName='AssetsTimestamp" + corpid + "';");
            towerfilter = "";
            groupfilter = "";
            fillFuelGrid(corpid);
            fillProcessGrid(corpid);
            // background fetch api
            if (!backgroundWorkerApi.IsBusy)
            {
                comboBoxChar.Enabled = false;
                comboBoxOwner.Enabled = false;
                comboBoxSystem.Enabled = false;
                backgroundWorkerApi.RunWorkerAsync();
                timer1m.Enabled = true;
            }
        }

        private void fillFuelGrid(long corpid)
        {
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            fn.writeDebug("--- fill fuelgrid ---");
            try
            {
                Image fbbar, stbar;
                con.Open();
                com.CommandText = "select moonName,towerName,fuelBlockQuantity,strontiumQuantity,charterQuantity,capacityPrimary,fuelBlockConsumption,strontiumConsumption,state,hasSov,stateTimestamp,capacitySecondary,itemID,fuelBlockTypeID,groupName,cleared,clearedBy,clearedTimestamp from apiStarbases where corpID=" + corpid + towerfilter + " order by moonName";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dataGridViewFuel.Rows.Clear();
                dr.Read();
                if (dr.HasRows)
                {
                    int col = 0;
                    string[] state = { "unanchored", "offline", "onlining", "reinforced", "online" };
                    int[] fbsum = { 0, 0, 0, 0 }, fbperday = { 0, 0, 0, 0 };
                    if (!comboBoxOwner.Items.Contains("- All -"))
                        comboBoxOwner.Items.Add("- All -");
                    if (!comboBoxSystem.Items.Contains("- All -"))
                        comboBoxSystem.Items.Add("- All -");
                    do
                    {
                        // fuel, max 875h (+25% sov)
                        double sovbonus = 1.0;
                        if (dr.GetBoolean(9))
                            sovbonus = 0.75;
                        if (Convert.ToDouble(dr.GetValue(5).ToString()) != 0)
                        {
                            double fuelbarlength = Convert.ToDouble(dr.GetValue(2).ToString()) * 5.0 / Convert.ToDouble(dr.GetValue(5).ToString());
                            double fuelbarsize = Convert.ToDouble(dr.GetValue(5).ToString()) / Math.Round(dr.GetInt32(6) * sovbonus, 0) / (140000 / 32 / 0.75);
                            DateTime dt = Convert.ToDateTime(dr.GetDateTime(10));
                            TimeSpan ts = DateTime.UtcNow.Subtract(dt);
                            double fuelhours = dr.GetInt32(2) / Math.Round(dr.GetInt32(6) * sovbonus, 0) - ts.Hours;
                            string fueltimestr = fn.hoursToDHstr(fuelhours);
                            if (dr.GetInt32(8) != 4) fueltimestr = "";
                            string ctime;
                            if (dr.GetInt32(4) > 0 && dr.GetInt32(8) == 4)
                                ctime = fn.hoursToDHstr(dr.GetInt32(4));
                            else ctime = "-";
                            fbbar = fn.customProgressBar(fueltimestr, fuelbarlength, fuelbarsize, Color.LightBlue, Color.White, 202, 16, true, this.Font.Name);
                            //  soll - ist days
                            double diff = Convert.ToDouble(numericUpDown1.Value) * 24 - fuelhours;
                            diff = Math.Round(diff * dr.GetInt32(6) * sovbonus, 0);
                            // strontium
                            string stronth = fn.hoursToDHstr(dr.GetInt32(3) / dr.GetInt32(7) / sovbonus);
                            double sval = (double)dr.GetInt32(3) * 3.0 / dr.GetFloat(11);
                            stbar = fn.customProgressBar(stronth, sval, 1.0, Color.LightGray, Color.White, 70, 16, false, this.Font.Name);
                            dataGridViewFuel.Rows.Add(dr.GetValue(0).ToString(), dr.GetValue(1).ToString(), state[dr.GetInt32(8)], fbbar, Convert.ToDouble(dr.GetInt32(2)), diff, dr.GetBoolean(9), stbar, ctime, dr.GetValue(14).ToString(), dr.GetDateTime(10), dr.GetValue(15), dr.GetValue(16), dr.GetValue(17));
                            dataGridViewFuel[3, col].ToolTipText = Math.Round(dr.GetInt32(6) * sovbonus) + " blocks/h, " + Math.Round(dr.GetFloat(5) / 5) + " max"; // max fuelblocks
                            dataGridViewFuel[6, col].ToolTipText = "Doubleclick to reset sovereignty, sov=true will be reset in npc/wh space on next update";
                            dataGridViewFuel[7, col].ToolTipText = Math.Floor(dr.GetFloat(11) / 3) + " max - " + dr.GetInt32(3) + " units = " + (Math.Floor(dr.GetFloat(11) / 3) - dr.GetInt32(3)) + " left"; // amount/max strontium
                            dataGridViewFuel[8, col].ToolTipText = dr.GetInt32(4) + " charters";
                            dataGridViewFuel[9, col].ToolTipText = "Enter a group name if you want a filter by group (F2/doubleclick)";
                            dataGridViewFuel[10, col].ToolTipText = "Timestamp of information, if reinforced time util tower comes out";
                            dataGridViewFuel[11, col].ToolTipText = "Doubleclick as reminder if you refueled";
                            // color refuel
                            if (diff < 0.0)
                            {
                                dataGridViewFuel[5, col].Style.ForeColor = Color.Green;
                                diff = 0.0;
                            }
                            else dataGridViewFuel[5, col].Style.ForeColor = Color.DarkRed;
                            // color status
                            if (dr.GetInt32(8) == 3) // reinforce
                            {
                                dataGridViewFuel[10, col].Style.BackColor = Color.LightPink;
                                dataGridViewFuel[2, col].Style.BackColor = Color.LightPink;
                            }
                            if (ts.Minutes > 130)
                                dataGridViewFuel[10, col].Style.ForeColor = Color.Red;
                            // hint (ItemID)
                            dataGridViewFuel[1, col].ToolTipText = "ID: " + dr.GetInt64(12).ToString();
                            dataGridViewFuel[1, col].Tag = dr.GetInt64(12).ToString();
                            // sum fuelblocks by type and owner
                            if (dr.GetInt32(13) == 4051) // caldari fuel
                            {
                                fbsum[0] += Convert.ToInt32(diff);
                                fbperday[0] += Convert.ToInt32(Math.Round(dr.GetInt32(6) * sovbonus, 0));
                            }
                            if (dr.GetInt32(13) == 4246) // minmatar fuel
                            {
                                fbsum[1] += Convert.ToInt32(diff);
                                fbperday[1] += Convert.ToInt32(Math.Round(dr.GetInt32(6) * sovbonus, 0));
                            }
                            if (dr.GetInt32(13) == 4247) // amarr fuel
                            {
                                fbsum[2] += Convert.ToInt32(diff);
                                fbperday[2] += Convert.ToInt32(Math.Round(dr.GetInt32(6) * sovbonus, 0));
                            }
                            if (dr.GetInt32(13) == 4312) // gallente fuel
                            {
                                fbsum[3] += Convert.ToInt32(diff);
                                fbperday[3] += Convert.ToInt32(Math.Round(dr.GetInt32(6) * sovbonus, 0));
                            }
                            // add owner name to dropdown
                            if (!comboBoxOwner.Items.Contains(dr.GetString(14)))
                                comboBoxOwner.Items.Add(dr.GetString(14));
                            // add system name to dropdown
                            if (dr.GetString(0).Contains(" "))
                            {
                                if (!comboBoxSystem.Items.Contains(dr.GetString(0).Substring(0, dr.GetString(0).IndexOf(" "))))
                                    comboBoxSystem.Items.Add(dr.GetString(0).Substring(0, dr.GetString(0).IndexOf(" ")));
                            }
                            col++;
                        }
                    }
                    while (dr.Read());
                    textBoxAmarrBlocks.Text = fbsum[2].ToString();
                    textBoxCaldariBlocks.Text = fbsum[0].ToString();
                    textBoxGallenteBlocks.Text = fbsum[3].ToString();
                    textBoxMinmatarBlocks.Text = fbsum[1].ToString();
                    textBoxIskRefuel.Text = string.Format("{0:0,0}",fbsum[0]*fn.fetchPrice(4051,false)+fbsum[1]*fn.fetchPrice(4246,false)+fbsum[2]*fn.fetchPrice(4247,false)+fbsum[3]*fn.fetchPrice(4312,false));
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                com.Dispose();
                con.Close();
                con.Dispose();
                MessageBox.Show(ex.Message, "SqlRead Fuel Grid");
            }
            dataGridViewFuel.ClearSelection();
        }

        private void fillProcessGrid(long corpid)
        {
            string matname, readstr, timetoact;
            double matqubic, matfillgrade, matamount, capacity, matvolume, qubicperhour, tact, timediff, maxtime, silobonus, smallerttact;
            int mattype, matgroup, width;
            bool rl;
            Color fg;
            Image matbar;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);

            if (fn.timestampcorpassets.Length == 19 && !fn.testdata)
            {
                DateTime dt = Convert.ToDateTime(fn.timestampcorpassets);
                TimeSpan ts = dt.Subtract(DateTime.UtcNow);
                timediff = 6.0 - ts.Hours;
            }
            else timediff = 0.0;

            fn.writeDebug("--- fill process grid ---");
            try
            {
                int col = 0;
                string wrsqlstr = "begin;\r\n";
                con.Open();
                com.CommandText = "select moonName,towerName,itemID,moonID,typeID,fold,remark,groupName from apiStarbases where corpID=" + corpid + towerfilter + " order by moonName";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                
                dataGridViewReaction.Rows.Clear();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // tower loop
                        SQLiteConnection con2 = new SQLiteConnection();
                        con2.ConnectionString = fn.connstr;
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        // pos modules
                        con2.Open();
                        //  and (input>0 or output>0 or contensTypeID=0)
                        com2.CommandText = "select itemID,typeID,groupID,capacity,contensTypeID,quantity,volume,input,output,remark,direction,cleared,clearedBy,clearedTimestamp";
                        com2.CommandText += " from reactions where groupID=404 and (input>0 or output>0 or contensTypeID=0) and locationID=" + dr.GetInt64(3) + " and corpID=" + corpid + " order by contensName,input";
                        fn.writeDebug("reader2: " + com2.CommandText);
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        smallerttact = 168.0;
                        // fetch silo bonus
                        readstr = fn.readStatSql("select valueFloat from dgmTypeAttributes where attributeID=757 and typeID=" + dr.GetInt32(4));
                        if (readstr != "")
                            silobonus = Convert.ToDouble(readstr);
                        else silobonus = 0.0;
                        dr2.Read();
                        if (dr2.HasRows)
                        {   // add tower name
                            matbar = new Bitmap(1, 1, System.Drawing.Imaging.PixelFormat.Format32bppArgb);
                            string bem = dr.GetValue(6).ToString();
                            if (bem == "")
                                bem = dr.GetValue(7).ToString();
                            dataGridViewReaction.Rows.Add(dr.GetValue(5), dr.GetValue(0).ToString(), dr.GetValue(1).ToString(), null, null, null, matbar, null, null, bem);
                            for (int i = 1; i <= 12; i++)
                                dataGridViewReaction[i, col].Style.BackColor = Color.LightGray;
                            dataGridViewReaction[0, col].ToolTipText = "Click to fold/unfold";
                            dataGridViewReaction[1, col].ToolTipText = "moonID: " + dr.GetInt64(3).ToString();
                            dataGridViewReaction[2, col].ToolTipText = "itemID: " + dr.GetInt64(2).ToString();
                            dataGridViewReaction[3, col].ReadOnly = true;
                            dataGridViewReaction[2, col].Tag = dr.GetInt64(2);

                            col++;
                            do
                            {   // add reaction parts
                                mattype = dr2.GetInt32(4);
                                matamount = Convert.ToDouble(dr2.GetInt32(5));

                                readstr = fn.readStatSql("select groupID,typeName from invTypes where typeID=" + mattype);
                                matgroup = Convert.ToInt32(fn.getSubStr(0, readstr));
                                if (mattype > 0)
                                {
                                    matname = fn.getSubStr(1, readstr);
                                    wrsqlstr += "update reactions set contensName='" + matname + "' where itemID=" + dr2.GetInt64(0) + " and corpID=" + corpid + ";\r\n";
                                    //wrsqlstr += "update reactions set contensName='" + matname + "', groupName='" + fn.strToSQLiteStr(dr.GetValue(7).ToString()) + "' where itemID=" + dr2.GetInt64(0) + " and corpID=" + corpid + ";\r\n";
                                }
                                else matname = "empty";
                                matvolume = dr2.GetDouble(6);
                                capacity = Convert.ToDouble(dr2.GetInt32(3));
                                capacity += capacity * silobonus / 100.0;

                                matqubic = Math.Round(matamount * matvolume, 1);

                                fg = Color.Tan; // all other
                                if (matgroup == 427) fg = Color.LightGreen; // moon raw mats
                                if (matgroup == 428) fg = Color.Silver; // intermediate mats
                                if (matgroup == 429) fg = Color.HotPink; // advanced products
                                if (matgroup == 712) fg = Color.SkyBlue; // biochemical products
                                if (matgroup == 303) fg = Color.DodgerBlue; // advanced biochemical products
                                if (matgroup == 974) fg = Color.MediumPurple; // polymer products
                                // filling or emptying
                                rl = true;
                                tact = 999.0;
                                maxtime = 192.0;
                                if (dr2.GetInt32(8) > dr2.GetInt32(7))
                                {   // mirror silo if its a sink
                                    matamount -= (dr2.GetInt32(8) - dr2.GetInt32(7)) * timediff;
                                    qubicperhour = (dr2.GetInt32(8) - dr2.GetInt32(7)) * matvolume;
                                    matqubic -= qubicperhour * timediff;
                                    tact = matqubic / qubicperhour;
                                    if (tact < 0.0) tact = 0.0;
                                    timetoact = fn.hoursToDHstr(tact);
                                    maxtime = capacity / qubicperhour;
                                    rl = false;
                                }
                                else if (dr2.GetInt32(8) < dr2.GetInt32(7))
                                {
                                    matamount += (dr2.GetInt32(7) - dr2.GetInt32(8)) * timediff;
                                    qubicperhour = (dr2.GetInt32(7) - dr2.GetInt32(8)) * matvolume;
                                    matqubic += qubicperhour * timediff;
                                    tact = (capacity - matqubic) / qubicperhour;
                                    if (tact < 0.0) tact = 0.0;
                                    timetoact = fn.hoursToDHstr(tact);
                                    maxtime = capacity / qubicperhour;
                                }
                                else timetoact = "";
                                matfillgrade = matamount * matvolume / capacity;
                                width = 402 * (int)maxtime / 336;
                                if (width > 402) width = 402;
                                matbar = fn.customProgressBar(timetoact.ToString(), matfillgrade, 1.0, fg, Color.White, width, 16, rl, this.Font.Name);
                                if (dr.GetValue(5).ToString() != closed)
                                {   // show only if its not fold
                                    dataGridViewReaction.Rows.Add(null, matname, fn.getItemName(dr2.GetInt64(0)), matamount, matqubic, dr2.GetInt32(8), matbar, dr2.GetInt32(7), dr2.GetValue(10), dr2.GetValue(9), dr2.GetValue(11), dr2.GetValue(12), dr2.GetValue(13));
                                    dataGridViewReaction[0, col].ToolTipText = "";
                                    dataGridViewReaction[2, col].Tag = dr2.GetInt64(0);
                                    dataGridViewReaction[1, col].ToolTipText = "typeID: " + dr2.GetInt32(4);
                                    dataGridViewReaction[2, col].ToolTipText = "itemID: " + dr2.GetInt64(0);
                                    dataGridViewReaction[6, col].ToolTipText = "current value: " + string.Format("{0:0,0}", matamount * fn.fetchPrice(mattype, true)) + " isk";
                                    dataGridViewReaction[8, col].ToolTipText = "Doubleclick to change direction if the silo shows the wrong orientation";
                                    dataGridViewReaction[9, col].ToolTipText = "F2/Doubleclick to enter a remark for this silo";
                                    dataGridViewReaction[10, col].ToolTipText = "Doubleclick as reminder if you cleared/filled the silo";
                                    col++;
                                }
                                else if (tact < smallerttact)
                                {
                                    smallerttact = tact;
                                    dataGridViewReaction[6, col - 1].Value = matbar;
                                }
                            }
                            while (dr2.Read());
                        }
                        dr2.Close();
                        dr2.Dispose();
                        con2.Close();
                        con2.Dispose();
                        com2.Dispose();
                    }
                    while (dr.Read());
                }
                dr.Close();
                if (Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + opened + "' and corpID=" + corpid)) > Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + closed + "' and corpID=" + corpid)))
                    dataGridViewReaction.Columns[0].HeaderText = opened;
                else dataGridViewReaction.Columns[0].HeaderText = closed;
                wrsqlstr += "commit;";
                fn.writeSql(wrsqlstr);
                con.Close();
                con.Dispose();
                com.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Process Grid");
            }
            dataGridViewReaction.ClearSelection();
        }

        private void fillIskGrid()
        {
            string matname, readstr;
            double matamount, iskperpart, iskperh, isksum, overallsum;
            int mattype, matgroup, amount;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);

            fn.writeDebug("--- fill isk grid ---");
            // remove old cached prices
            foreach (FileInfo f in new DirectoryInfo(fn.filepath).GetFiles("Isk *.xml"))
            {
                if (f.LastWriteTimeUtc.Subtract(DateTime.UtcNow).TotalMinutes < -32.0)
                    f.Delete();
            }

            try
            {
                int col = 0;
                string wrsqlstr = "begin;\r\n";
                con.Open();
                com.CommandText = "select moonName,towerName,itemID,moonID,typeID,fold,fuelBlockTypeID,fuelBlockQuantity,fuelBlockConsumption,hasSov from apiStarbases where corpID=" + corpid + towerfilter + " order by moonName";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    overallsum = 0.0;
                    dataGridViewIsk.Rows.Clear();
                    do
                    {   // tower loop
                        SQLiteConnection con2 = new SQLiteConnection();
                        con2.ConnectionString = fn.connstr;
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        // pos modules
                        con2.Open();
                        //  and (input>0 or output>0 or contensTypeID=0)
                        com2.CommandText = "select itemID,typeID,groupID,capacity,contensTypeID,quantity,volume,input,output,remark,direction from reactions where groupID=404 and (input>0 or output>0 or contensTypeID=0) and locationID=" + dr.GetInt64(3) + " and corpID=" + corpid + " order by contensTypeID,input";
                        fn.writeDebug("reader2: " + com2.CommandText);
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        // fetch silo bonus
                        readstr = fn.readStatSql("select valueFloat from dgmTypeAttributes where attributeID=757 and typeID=" + dr.GetInt32(4));
                        dr2.Read();
                        // add tower name
                        iskperpart = fn.fetchPrice(dr.GetInt32(6), false);
                        amount = dr.GetInt32(8);
                        if (dr.GetBoolean(9))
                            amount = Convert.ToInt32(Math.Ceiling(0.75 * (double)dr.GetInt32(8)));
                        isksum = iskperh = -iskperpart * amount;
                        overallsum += isksum;
                        dataGridViewIsk.Rows.Add(dr.GetValue(5), dr.GetValue(0).ToString(), dr.GetValue(1).ToString(), dr.GetInt32(7), amount, 0, iskperpart, iskperh, iskperh * 24, iskperh * 24 * 7, iskperh * 24 * 30);
                        for (int i = 1; i <= 10; i++)
                            dataGridViewIsk[i, col].Style.BackColor = Color.LightGray;
                        dataGridViewIsk[1, col].ToolTipText = "moonID: " + dr.GetInt64(3).ToString();
                        dataGridViewIsk[2, col].ToolTipText = "itemID: " + dr.GetInt64(2).ToString();
                        dataGridViewIsk[0, col].ToolTipText = "Click to fold/unfold";
                        dataGridViewIsk[3, col].ReadOnly = true;
                        dataGridViewIsk[2, col].Tag = dr.GetInt64(2);
                        col++;
                        
                        if (dr2.HasRows)
                        {   
                            do
                            {   // add reaction parts
                                mattype = dr2.GetInt32(4);
                                matamount = Convert.ToDouble(dr2.GetInt32(5));

                                readstr = fn.readStatSql("select groupID,typeName from invTypes where typeID=" + mattype);
                                matgroup = Convert.ToInt32(fn.getSubStr(0, readstr));
                                if (mattype > 0)
                                {
                                    matname = fn.getSubStr(1, readstr);
                                }
                                else matname = "empty";

                                // collect isk
                                amount = dr2.GetInt32(7) - dr2.GetInt32(8);
                                iskperpart = fn.fetchPrice(dr2.GetInt32(4), false);
                                iskperh = iskperpart * amount;
                                isksum += iskperh;
                                overallsum += iskperh;

                                if (dr.GetValue(5).ToString() != closed)
                                {   // show only if its not fold
                                    dataGridViewIsk.Rows.Add(null, matname, fn.getItemName(dr2.GetInt64(0)), matamount, dr2.GetInt32(8), dr2.GetInt32(7), iskperpart, iskperh, iskperh * 24, iskperh * 24 * 7, iskperh * 24 * 30);
                                    dataGridViewIsk[2, col].Tag = dr2.GetInt64(0);
                                    dataGridViewIsk[1, col].ToolTipText = "typeID: " + dr2.GetInt32(4);
                                    dataGridViewIsk[2, col].ToolTipText = "itemID: " + dr2.GetInt64(0);
                                    dataGridViewIsk[0, col].ToolTipText = "";
                                    col++;
                                }
                            }
                            while (dr2.Read());

                            if (dr.GetValue(5).ToString() != closed)
                            {
                                dataGridViewIsk.Rows.Add(null, "", "", "", "", "", "Sum:", isksum, isksum * 24, isksum * 24 * 7, isksum * 24 * 30);
                                for (int i = 6; i <= 10; i++)
                                {
                                    if(isksum >= 0)
                                        dataGridViewIsk[i, col].Style.BackColor = Color.LightGreen;
                                    else dataGridViewIsk[i, col].Style.BackColor = Color.LightSalmon;
                                }
                                col++;
                            }
                            else
                            {
                                dataGridViewIsk[6, col - 1].Value = "Sum:"; dataGridViewIsk[7, col - 1].Value = isksum;
                                dataGridViewIsk[8, col - 1].Value = isksum * 24; dataGridViewIsk[9, col - 1].Value = isksum * 24 * 7; dataGridViewIsk[10, col - 1].Value = isksum * 24 * 30;
                                for (int i = 6; i <= 10; i++)
                                {
                                    if (isksum >= 0)
                                        dataGridViewIsk[i, col-1].Style.BackColor = Color.LightGreen;
                                    else dataGridViewIsk[i, col-1].Style.BackColor = Color.LightSalmon;
                                }
                            }
                        }
                        dr2.Close();
                        dr2.Dispose();
                        com2.Dispose();
                        con2.Close();
                        con2.Dispose();
                    }
                    while (dr.Read());
                    // overallsum
                    dataGridViewIsk.Rows.Add(null, "", "", "", "", "", "", "", "", "");
                    col++;
                    dataGridViewIsk.Rows.Add(null, "Overall", "", "", "", "", "Sum:", overallsum, overallsum * 24, overallsum * 24 * 7, overallsum * 24 * 30);
                    for (int i = 1; i <= 10; i++)
                    {
                        if (overallsum >= 0)
                            dataGridViewIsk[i, col].Style.BackColor = Color.LightGreen;
                        else dataGridViewIsk[i, col].Style.BackColor = Color.LightSalmon;
                    }
                    col++;
                }
                dr.Close();
                if (Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + opened + "' and corpID=" + corpid)) > Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + closed + "' and corpID=" + corpid)))
                    dataGridViewIsk.Columns[0].HeaderText = opened;
                else dataGridViewIsk.Columns[0].HeaderText = closed;
                wrsqlstr += "commit;";
                fn.writeSql(wrsqlstr);

                // Materials needed from external source
                com.CommandText = "select contensName,sum(quantity),sum(output),sum(input),contensTypeID from reactions where (input>0 or output>0) and corpID=" + corpid + groupfilter + " group by contensName order by contensName";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                bool addchapter = false;
                if (dr.HasRows)
                {
                    do
                    {
                        double matpreis = fn.fetchPrice(dr.GetInt32(4), false);
                        if ((dr.GetInt32(2) - dr.GetInt32(3)) > 0)
                        {
                            if(!addchapter)
                            {
                                addchapter = true;
                                dataGridViewIsk.Rows.Add(null, "", "", "", "", "", "", "", "", "", "");
                                col++;
                                dataGridViewIsk.Rows.Add(null, "Missing material", "(only filter by group applicable)", "quantity", "in/h", "out/h", "isk/unit", "buy/h", "buy/d", "buy/w", "buy/m");
                                for (int i = 1; i <= 10; i++)
                                    dataGridViewIsk[i, col].Style.BackColor = Color.LightGray;
                                col++;
                            }
                            dataGridViewIsk.Rows.Add(null, dr.GetString(0), "needed from external source", dr.GetInt32(1), dr.GetInt32(2), dr.GetInt32(3), matpreis, matpreis * (dr.GetInt32(3) - dr.GetInt32(2)), matpreis * 24 * (dr.GetInt32(3) - dr.GetInt32(2)), matpreis * 24 * 7 * (dr.GetInt32(3) - dr.GetInt32(2)), matpreis * 24 * 30 * (dr.GetInt32(3) - dr.GetInt32(2)));
                            // show missing units in tooltip
                            dataGridViewIsk[7, col].ToolTipText = (dr.GetInt32(2) - dr.GetInt32(3)).ToString("N0") + " units/h";
                            dataGridViewIsk[8, col].ToolTipText = ((dr.GetInt32(2) - dr.GetInt32(3)) * 24).ToString("N0") + " units/d";
                            dataGridViewIsk[9, col].ToolTipText = ((dr.GetInt32(2) - dr.GetInt32(3)) * 24 * 7).ToString("N0") + " units/w";
                            dataGridViewIsk[10, col].ToolTipText = ((dr.GetInt32(2) - dr.GetInt32(3)) * 24 * 7 * 30).ToString("N0") + " units/m";
                            for (int i = 1; i <= 10; i++)
                                dataGridViewIsk[i, col].Style.BackColor = Color.LightSalmon;
                            col++;
                        }
                    }
                    while (dr.Read());
                }
                dr.Close();
                // sum Materials
                com.CommandText = "select contensName,sum(quantity),sum(output),sum(input),contensTypeID,volume from reactions where input>0 and output=0 and corpID=" + corpid + groupfilter + " group by contensName order by contensName";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    double gsum = 0.0, sumq = 0.0;
                    dataGridViewIsk.Rows.Add(null, "", "", "", "", "", "", "", "", "", "");
                    col++;
                    dataGridViewIsk.Rows.Add(null, "Output silo sum", "(only filter by group applicable)", "quantity", "in/h", "out/h", "isk/unit", "", "", "m³", "isk");
                    for (int i = 1; i <= 10; i++)
                        dataGridViewIsk[i, col].Style.BackColor = Color.LightGray;
                    col++;
                    do
                    {
                        double matpreis = fn.fetchPrice(dr.GetInt32(4), false);
                        dataGridViewIsk.Rows.Add(null, dr.GetString(0), "current silo contens:", dr.GetInt32(1), dr.GetInt32(2), dr.GetInt32(3), matpreis, "", "", dr.GetInt32(1) * dr.GetDouble(5), matpreis * dr.GetInt32(1));
                        col++;
                        gsum += matpreis * dr.GetInt32(1);
                        sumq += dr.GetInt32(1) * dr.GetDouble(5);
                    }
                    while (dr.Read());
                    if (gsum > 0.0)
                    {
                        dataGridViewIsk.Rows.Add(null, "Produced sum", "current silo contens value", "", "", "", "", "", "", sumq, gsum);
                        for (int i = 1; i <= 10; i++)
                            dataGridViewIsk[i, col].Style.BackColor = Color.LightGreen;
                        col++;
                    }
                }
                dr.Close();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Isk Grid");
            }
            dataGridViewIsk.ClearSelection();
        }

        private void fillAmmunitionGrid()
        {
            string ammoname, ammoquantity, ammotype, rstr;

            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);

            if (Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + opened + "' and corpID=" + corpid)) > Convert.ToInt32(fn.readSql("select count(itemID) from apiStarbases where fold='" + closed + "' and corpID=" + corpid)))
                dataGridViewAmmo.Columns[0].HeaderText = opened;
            else dataGridViewAmmo.Columns[0].HeaderText = closed;

            try
            {
                int col = 0;
                con.Open();
                if (comboBoxOwner.Text == "" || comboBoxOwner.Text == "- All -")
                    com.CommandText = "select moonName,towerName,itemID,moonID,typeID,fold,remark,groupName from apiStarbases where corpID=" + corpid + " order by moonName";
                else com.CommandText = "select moonName,towerName,itemID,moonID,typeID,fold,remark,groupName from apiStarbases where corpID=" + corpid + towerfilter + " order by moonName";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    dataGridViewAmmo.Rows.Clear();
                    do
                    {   // tower loop
                        SQLiteConnection con2 = new SQLiteConnection();
                        con2.ConnectionString = fn.connstr;
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        // pos modules
                        con2.Open();
                        com2.CommandText = "select itemID,typeID from apiAssets where (groupID=417 or groupID=426 or groupID=430 or groupID=449) and locationID=" + dr.GetInt64(3) + " and corpID=" + corpid + " order by typeID";
                        fn.writeDebug("reader: " + com2.CommandText);
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        dr2.Read();
                        if (dr2.HasRows)
                        {   // add tower name
                            dataGridViewAmmo.Rows.Add(dr.GetValue(5), dr.GetValue(0).ToString(), dr.GetValue(1).ToString());
                            for (int i = 1; i < 3; i++)
                                dataGridViewAmmo[i, col].Style.BackColor = Color.LightGray;
                            dataGridViewAmmo[1, col].ToolTipText = "moonID: " + dr.GetInt64(3).ToString();
                            dataGridViewAmmo[2, col].ToolTipText = "itemID: " + dr.GetInt64(2).ToString();
                            dataGridViewAmmo[0, col].ToolTipText = "Click to fold/unfold";
                            dataGridViewAmmo[2, col].Tag = dr.GetInt64(2);
                            col++;
                            do
                            {   // add reaction parts
                                if (dr.GetValue(5).ToString() != closed)
                                {   // show only if its not fold
                                    rstr = fn.readSql("select typeID,sum(quantity),itemID from apiAssets where parentItemID=" + dr2.GetInt64(0) + " group by typeID");
                                    if (rstr != "")
                                    {
                                        ammotype = fn.getSubStr(0, rstr);
                                        ammoquantity = fn.getSubStr(1, rstr);
                                        ammoname = fn.readStatSql("select typeName from invTypes where typeID=" + ammotype);
                                    }
                                    else
                                    {
                                        ammotype = "0";
                                        ammoname = "";
                                        ammoquantity = "0";
                                    }
                                    dataGridViewAmmo.Rows.Add(null, "", fn.getItemName(dr2.GetInt64(0)),ammoname,ammoquantity);
                                    dataGridViewAmmo[2, col].ToolTipText = "itemID: " + dr2.GetInt64(0).ToString();
                                    dataGridViewAmmo[3, col].ToolTipText = "typeID: " + ammotype;
                                    col++;
                                    rstr = fn.readSql("select typeID,quantity,itemID from apiAssets where typeID<>" + ammotype + " and parentItemID=" + dr2.GetInt64(0));
                                    if (rstr != "")
                                    {
                                        ammotype = fn.getSubStr(0, rstr);
                                        ammoquantity = fn.getSubStr(1, rstr);
                                        ammoname = fn.readStatSql("select typeName from invTypes where typeID=" + ammotype);
                                        dataGridViewAmmo.Rows.Add(null, "", "", ammoname, ammoquantity);
                                        dataGridViewAmmo[3, col].ToolTipText = "typeID: " + ammotype;
                                        col++;
                                    }
                                }
                            }
                            while (dr2.Read());
                        }
                        dr2.Close();
                        dr2.Dispose();
                        com2.Dispose();
                        con2.Close();
                        con2.Dispose();
                    }
                    while (dr.Read());
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Ammunition Grid");
            }
        }

        private void fillFittingGrid()
        {
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {   
                int col = 0;
                long cpu = 0, pg = 0, poscpu = 0, pospg = 0, sumcpu = 0, sumpg = 0, oldlocation = 0;
                string itemname = "", locationname="",towername="",vi,filtersystem="";
                dataGridViewFitting.Rows.Clear();
                con.Open();
                if (comboBoxSystem.Text != null && comboBoxSystem.Text != "- All -")    
                    filtersystem = fn.readStatSql("select itemID from invNames where itemName='" + fn.strToSQLiteStr(comboBoxSystem.Text) + "'");
                if(filtersystem!="" && filtersystem.StartsWith("3"))
                    com.CommandText = "select locationID,typeID,itemID,count(itemID),groupID from apiAssets where systemID="+filtersystem+" and categoryID=23 and depths=1 and rawQuantity=-1 and corpID=" + corpid + " group by locationID,typeID order by locationID,groupID,typeID;";
                else com.CommandText = "select locationID,typeID,itemID,count(itemID),groupID from apiAssets where categoryID=23 and depths=1 and rawQuantity=-1 and corpID=" + corpid + " group by locationID,typeID order by locationID,groupID,typeID;";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        if (oldlocation != dr.GetInt64(0))
                        {
                            if (oldlocation > 0)
                            {
                                dataGridViewFitting.Rows.Add("Summary", "(does and can not take offline into account)", "", poscpu - sumcpu, pospg - sumpg);
                                dataGridViewFitting[0, col].Style.BackColor = Color.LightGray; dataGridViewFitting[1, col].Style.BackColor = Color.LightGray;
                                dataGridViewFitting[2, col].Style.BackColor = Color.LightGray; dataGridViewFitting[3, col].Style.BackColor = Color.LightGray;
                                dataGridViewFitting[4, col].Style.BackColor = Color.LightGray;
                                col++;
                                sumcpu = 0;
                                sumpg = 0;
                            }
                            oldlocation = dr.GetInt64(0);
                        }
                        itemname = fn.readStatSql("select typeName from invTypes where typeID=" + dr.GetInt32(1));
                        locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr.GetInt64(0));
                        if(dr.GetInt32(4)==365)
                        {
                            poscpu = Convert.ToInt64(fn.readStatSql("select valueInt from dgmTypeAttributes where attributeID=48 and typeID=" + dr.GetInt32(1)));
                            pospg = Convert.ToInt64(fn.readStatSql("select valueInt from dgmTypeAttributes where attributeID=11 and typeID=" + dr.GetInt32(1)));
                            towername=fn.readSql("select towerName from apiStarbases where itemID=" + dr.GetInt64(2));
                            dataGridViewFitting.Rows.Add(locationname, towername, dr.GetInt32(3), poscpu, pospg);
                            col++;
                        }
                        else
                        {
                            vi = fn.readStatSql("select valueInt from dgmTypeAttributes where attributeID=50 and typeID=" + dr.GetInt32(1));
                            if (vi != "") cpu = Convert.ToInt64(vi);
                            else
                            {
                                cpu = 0;
                                vi = fn.readStatSql("select valueFloat from dgmTypeAttributes where attributeID=50 and typeID=" + dr.GetInt32(1));
                                if (vi != "") cpu = Convert.ToInt64(vi);
                            }
                            vi = fn.readStatSql("select valueInt from dgmTypeAttributes where attributeID=30 and typeID=" + dr.GetInt32(1));
                            if (vi != "") pg = Convert.ToInt64(vi);
                            else
                            {
                                pg = 0;
                                vi = fn.readStatSql("select valueFloat from dgmTypeAttributes where attributeID=30 and typeID=" + dr.GetInt32(1));
                                if (vi != "") pg = Convert.ToInt64(vi);
                            }
                            sumcpu += cpu * dr.GetInt32(3);
                            sumpg += pg * dr.GetInt32(3);
                            dataGridViewFitting.Rows.Add(locationname, itemname, dr.GetInt32(3), cpu * dr.GetInt32(3), pg * dr.GetInt32(3));
                            dataGridViewFitting[1, col].ToolTipText = "typeID=" + dr.GetInt32(1);
                            col++;
                        }
                    }
                    while (dr.Read());
                    dataGridViewFitting.Rows.Add("Summary", "(does and can not take offline into account)", "", poscpu - sumcpu, pospg - sumpg);
                    dataGridViewFitting[0, col].Style.BackColor = Color.LightGray; dataGridViewFitting[1, col].Style.BackColor = Color.LightGray;
                    dataGridViewFitting[2, col].Style.BackColor = Color.LightGray; dataGridViewFitting[3, col].Style.BackColor = Color.LightGray;
                    dataGridViewFitting[4, col].Style.BackColor = Color.LightGray;
                }
                dr.Close();
                con.Close();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Process Grid");
            }
        }

        private void fillPrivAssetsGrid()
        {
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {   // get fuel
                int col = 0, typeid = 0;
                string itemname = "", locationname,flagtext;
                dataGridViewPrAssets.Rows.Clear();
                con.Open();
                com.CommandText = "select typeID,quantity,locationID,flag,depths,parentItemID from apiAssets where (groupID=1136 or typeID=16275) and charID=" + charid + " and parentItemID not in (select itemID from apiStarbases) order by typeID";
                fn.writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        if (typeid != dr.GetInt32(0))
                        {
                            typeid = dr.GetInt32(0);
                            itemname = fn.readStatSql("select typeName from invTypes where typeID=" + typeid);
                        }
                        locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr.GetInt64(2));
                        if (locationname == "")
                            locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr.GetInt64(2));
                        flagtext = fn.hangarname(dr.GetInt16(3), dr.GetInt16(4), dr.GetInt64(5));
                        dataGridViewPrAssets.Rows.Add(itemname, dr.GetInt32(1), locationname, flagtext, fn.getItemName(dr.GetInt64(5)));
                        dataGridViewPrAssets[0, col].Style.BackColor = Color.PowderBlue; dataGridViewPrAssets[1, col].Style.BackColor = Color.PowderBlue;
                        col++;
                    }
                    while (dr.Read());
                }
                dr.Close();
                // get materials
                com.CommandText = "select contensTypeID,contensName from reactions where contensTypeID<>0 and contensName<>'' and corpID=" + corpid + " group by contensTypeID order by contensName";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        SQLiteConnection con2 = new SQLiteConnection();
                        con2.ConnectionString = fn.connstr;
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        con2.Open();
                        com2.CommandText = "select quantity,locationID,flag,depths,parentItemID from apiAssets where typeID=" + dr.GetInt32(0) + " and quantity>1 and charID=" + charid + " and parentItemID not in (select itemID from reactions where charID=" + charid + ")";
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        dr2.Read();
                        if (dr2.HasRows)
                        {
                            do
                            {
                                locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr2.GetInt64(1));
                                if (locationname == "")
                                    locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr2.GetInt64(1));
                                flagtext = fn.hangarname(dr2.GetInt16(2), dr2.GetInt16(3), dr2.GetInt64(4));
                                dataGridViewPrAssets.Rows.Add(dr.GetValue(1), dr2.GetInt32(0), locationname, flagtext, fn.getItemName(dr2.GetInt64(4)));
                                dataGridViewPrAssets[0, col].Style.BackColor = Color.LemonChiffon; dataGridViewPrAssets[1, col].Style.BackColor = Color.LemonChiffon;
                                col++;
                            }
                            while (dr2.Read());
                        }
                        dr2.Close();
                        con2.Close();
                    }
                    while (dr.Read());
                }
                dr.Close();
                con.Close();
                // pos parts (not mounted)
                con.Open();
                com.CommandText = "select typeID,quantity,locationID,flag,depths,parentItemID from apiAssets where categoryID=23 and (rawQuantity=0 or flag>0) and charID=" + charid + " group by typeID order by typeID";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        if (typeid != dr.GetInt32(0))
                        {
                            typeid = dr.GetInt32(0);
                            itemname = fn.readStatSql("select typeName from invTypes where typeID=" + typeid);
                        }
                        locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr.GetInt64(2));
                        if (locationname == "")
                            locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr.GetInt64(2));
                        flagtext = fn.hangarname(dr.GetInt16(3), dr.GetInt16(4), dr.GetInt64(5));
                        dataGridViewPrAssets.Rows.Add(itemname, dr.GetInt32(1), locationname, flagtext, fn.getItemName(dr.GetInt64(5)));
                        dataGridViewPrAssets[0, col].Style.BackColor = Color.PaleGreen; dataGridViewPrAssets[1, col].Style.BackColor = Color.PaleGreen;
                        col++;
                    }
                    while (dr.Read());
                }
                dr.Close();
                con.Close();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Assets Grid");
            }
        }

        private void fillAssetsGrid()
        {
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {   // get fuel
                int col = 0,typeid=0;
                string itemname="",locationname,flagtext;
                dataGridViewTowerAssets.Rows.Clear();
                con.Open();
                com.CommandText = "select typeID,quantity,locationID,flag,depths,parentItemID from apiAssets where (groupID=1136 or typeID=16275) and corpID=" + corpid + " and parentItemID not in (select itemID from apiStarbases) order by typeID";
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        if (typeid != dr.GetInt32(0))
                        {
                            typeid = dr.GetInt32(0);
                            itemname = fn.readStatSql("select typeName from invTypes where typeID="+typeid);
                        }
                        locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr.GetInt64(2));
                        if (locationname == "")
                            locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr.GetInt64(2));
                        flagtext = fn.hangarname(dr.GetInt16(3), dr.GetInt16(4), dr.GetInt64(5));
                        dataGridViewTowerAssets.Rows.Add(itemname, dr.GetInt32(1), locationname, flagtext, fn.getItemName(dr.GetInt64(5)));
                        dataGridViewTowerAssets[0, col].Style.BackColor = Color.PowderBlue; dataGridViewTowerAssets[1, col].Style.BackColor = Color.PowderBlue;
                        col++;
                    }
                    while (dr.Read());
                }
                dr.Close();
                // get materials
                com.CommandText = "select contensTypeID,contensName from reactions where contensTypeID<>0 and contensName<>'' and corpID="+corpid+" group by contensTypeID order by contensName";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        SQLiteConnection con2 = new SQLiteConnection();
                        con2.ConnectionString = fn.connstr;
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        con2.Open();
                        com2.CommandText = "select quantity,locationID,flag,depths,parentItemID from apiAssets where typeID=" + dr.GetInt32(0) + " and quantity>1 and corpID=" + corpid + " and parentItemID not in (select itemID from reactions where corpID=" + corpid + ")";
                        fn.writeDebug("reader: " + com2.CommandText);
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        dr2.Read();
                        if (dr2.HasRows)
                        {
                            do
                            {
                                locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr2.GetInt64(1));
                                if (locationname == "")
                                    locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr2.GetInt64(1));
                                flagtext = fn.hangarname(dr2.GetInt16(2), dr2.GetInt16(3), dr2.GetInt64(4));
                                dataGridViewTowerAssets.Rows.Add(dr.GetValue(1), dr2.GetInt32(0), locationname, flagtext, fn.getItemName(dr2.GetInt64(4)));
                                dataGridViewTowerAssets[0, col].Style.BackColor = Color.LemonChiffon; dataGridViewTowerAssets[1, col].Style.BackColor = Color.LemonChiffon;
                                col++;
                            }
                            while (dr2.Read());
                        }
                        dr2.Close();
                        con2.Close();
                    }
                    while (dr.Read());
                }
                dr.Close();
                con.Close();
                // pos parts (not mounted)
                con.Open();
                com.CommandText = "select typeID,quantity,locationID,flag,depths,parentItemID from apiAssets where categoryID=23 and (rawQuantity=0 or flag>0) and corpID=" + corpid + " group by typeID order by typeID";
                fn.writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        if (typeid != dr.GetInt32(0))
                        {
                            typeid = dr.GetInt32(0);
                            itemname = fn.readStatSql("select typeName from invTypes where typeID=" + typeid);
                        }
                        locationname = fn.readSql("select stationName from apiOutposts where stationID=" + dr.GetInt64(2));
                        if (locationname == "")
                            locationname = fn.readStatSql("select itemName from invNames where itemID=" + dr.GetInt64(2));
                        flagtext = fn.hangarname(dr.GetInt16(3), dr.GetInt16(4), dr.GetInt64(5));
                        dataGridViewTowerAssets.Rows.Add(itemname, dr.GetInt32(1), locationname, flagtext, fn.getItemName(dr.GetInt64(5)));
                        dataGridViewTowerAssets[0, col].Style.BackColor = Color.PaleGreen; dataGridViewTowerAssets[1, col].Style.BackColor = Color.PaleGreen;
                        col++;
                    }
                    while (dr.Read());
                }
                dr.Close();
                con.Close();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead Assets Grid");
            }
        }

        private void getUserList()
        {
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = fn.connstruser;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                con.Open();
                com.CommandText = "select name from user order by name;";
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    comboBoxUser.Items.Clear();
                    comboBoxUser.Items.Add(dr.GetString(0));
                    comboBoxChar.Items.Clear();
                    comboBoxChar.Items.Add(dr.GetString(0));
                    while (dr.Read())
                    {
                        comboBoxChar.Items.Add(dr.GetString(0));
                        comboBoxUser.Items.Add(dr.GetString(0));
                    }
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                com.Dispose();
                con.Close();
                con.Dispose();
                MessageBox.Show(ex.Message, "SqlRead user");
            }
        }

        private void buttonDeleteUser_Click(object sender, EventArgs e)
        {
            if (fn.readUserSql("select name from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'") != "")
                fn.readUserSql("delete from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'");
            comboBoxUser.Text = "";
            textBoxId.Text = "";
            textBoxvCode.Text = "";
            textBoxCorpName.Text = "";
            getUserList();
        }

        private void buttonSaveCharKey_Click(object sender, EventArgs e)
        {
            if (textBoxId.Text != "" && textBoxvCode.Text != "" && comboBoxUser.Text != "")
            {
                if (fn.readUserSql("select name from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'") == "")
                {
                    fn.writeUserSql("insert into user (name) values('" + fn.strToSQLiteStr(comboBoxUser.Text) + "')");
                }
                getUserList();
                string xmlstr = fn.fetchApi(fn.apiserver + "account/Characters.xml.aspx?keyID=" + textBoxId.Text + "&vCode=" + textBoxvCode.Text);
                if (xmlstr.Contains("<error code="))
                {
                    MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Api");
                    return;
                }
                else if (xmlstr == "")
                {
                    MessageBox.Show("Empty return", "Api");
                    return;
                }

                string strline = "";
                StringReader strread = new StringReader(xmlstr);
                bool found = false;
                while (!found && (strline = strread.ReadLine()) != null)
                {   // get line with current username
                    if (strline.Contains(comboBoxUser.Text))
                        found = true;
                }
                if (!found)
                {
                    MessageBox.Show("Character not in Api", "Api");
                    return;
                }
                string charid = fn.fetchXmlValue("characterID=", strline);
                string corpid = fn.fetchXmlValue("corporationID=", strline);
                string corpname = fn.fetchXmlValue("corporationName=", strline);
                textBoxCorpName.Text = corpname;
                if (charid == "" || corpid == "" || corpname == "")
                {
                    MessageBox.Show("Character id could not be read", "Api");
                    return;
                }
                string encapi = fn.Encrypt(textBoxId.Text + "|0|" + textBoxvCode.Text, fn.comname);
                if (0 < fn.writeUserSql("update user set charID=" + charid + ",corpID=" + corpid + ",code='" + encapi + "' where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'"))
                    MessageBox.Show("Character Api valid and stored", "Api");
            }
        }

        private void comboBoxUser_SelectedIndexChanged(object sender, EventArgs e)
        {
            textBoxExportKey.Text = "";
            buttonImportKey.Enabled = false;
            string corpid = fn.readUserSql("select corpID,charID from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'");
            string charid = corpid.Substring(corpid.IndexOf("|") + 1);
            corpid = corpid.Substring(0, corpid.IndexOf("|"));
            if (corpid != "")
            {
                string corpname = fn.readUserSql("select name from corp where corpID=" + corpid);
                if (corpname != "")
                    textBoxCorpName.Text = corpname;
                else
                    textBoxCorpName.Text = corpid;
            }
            else
            {
                textBoxCorpName.Text = "";
                return;
            }
            string encapi = fn.readUserSql("select code from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'");
            if (encapi == "")
            {
                textBoxId.Text = "";
                textBoxvCode.Text = "";
                return;
            }
            string ret = fn.Decrypt(encapi, fn.comname);

            textBoxId.Text = fn.getSubStr(0,ret);
            // tag = fn.getSubStr(1,ret);
            textBoxvCode.Text = fn.getSubStr(2,ret);

            buttonSaveCharKey.Enabled = true;
            buttonSaveCorpKey.Enabled = true;
            buttonDeleteUser.Enabled = true;
            // maybe director
            string xmlstr = fn.fetchApi(fn.apiserver + "char/CharacterSheet.xml.aspx?keyID=" + textBoxId.Text + "&vCode=" + textBoxvCode.Text + "&characterID=" + charid);
            directorRole = xmlstr.Contains("roleDirector");
            if (directorRole)
            {
                if(textBoxExportName.Text!="")
                    buttonExportKey.Enabled = true;
                if (corpid != "")
                    linkLabel2.Text = "https://community.eveonline.com/support/api-key/CreatePredefined?accessMask=134217738" + fn.readUserSql("select charID from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'") + "/true";
                else linkLabel2.Text = "https://community.eveonline.com/support/api-key/CreatePredefined?accessMask=134217738";
            }
            else
            {
                buttonExportKey.Enabled = false;
                linkLabel2.Text = "https://community.eveonline.com/support/api-key/CreatePredefined?accessMask=134217738";
            }
        }

        private void textBoxExportName_TextChanged(object sender, EventArgs e)
        {
            if (directorRole && textBoxExportName.Text != "")
                buttonExportKey.Enabled = true;
            else buttonExportKey.Enabled = false;
            textBoxExportKey.Text = "";
        }

        private void buttonExportKey_Click(object sender, EventArgs e)
        {
            if (textBoxExportName.Text == "")
            {
                textBoxExportKey.Text = "";
                MessageBox.Show("Need a character name for export");
                return;
            }
            string encapi = fn.readUserSql("select code from corp where name='" + fn.strToSQLiteStr(textBoxCorpName.Text) + "'");
            string decapi = fn.Decrypt(encapi, fn.comname);
            string code = "";
            string encexapi = fn.Encrypt(code + "|" + decapi, textBoxExportName.Text); // add a code to export api (later maybe user rights)
            textBoxExportKey.Text = encexapi;
        }

        private void buttonSaveCorpKey_Click(object sender, EventArgs e)
        {
            if (textBoxCorpName.Text == "" || textBoxCorpId.Text == "" || textBoxCorpvCode.Text == "")
                return;
            string xmlstr = fn.fetchApi(fn.apiserver + "corp/CorporationSheet.xml.aspx?keyID=" + textBoxCorpId.Text + "&vCode=" + textBoxCorpvCode.Text);
            if (xmlstr.Contains("<error code="))
                MessageBox.Show(fn.fetchXmlAttrib("<error code=", xmlstr), "Corp Api");
            else if (xmlstr == "")
                MessageBox.Show("Empty return", "Corp Api");
            else
            {
                if (textBoxCorpName.Text == fn.fetchXmlAttrib("corporationName", xmlstr) || textBoxCorpName.Text == fn.fetchXmlAttrib("corporationID", xmlstr))
                {
                    string encapi = fn.Encrypt(textBoxCorpId.Text + "|" + textBoxCorpvCode.Text, fn.comname);
                    encapi.Replace("'", "''");
                    fn.writeUserSql("replace into corp values(" + fn.fetchXmlAttrib("corporationID", xmlstr) + ",'" + fn.strToSQLiteStr(fn.fetchXmlAttrib("corporationName", xmlstr)) + "','" + encapi + "',0);");
                    textBoxCorpName.Text = fn.fetchXmlAttrib("corporationName", xmlstr);
                    corpname = fn.fetchXmlAttrib("corporationName", xmlstr);
                    //fn.writeUserSql("update corp set code='" + encapi + "' where name='" + textBoxCorpName.Text + "'");
                    MessageBox.Show("Corp API checked and key stored");
                }
                else
                    MessageBox.Show("Corp API key does not match the corp of the selected Character");
            }
        }

        private void buttonSaveEncCorpKey_Click(object sender, EventArgs e)
        {
            if (textBoxCorpName.Text == "" || textBoxImportKey.Text == "")
                return;
            string decapi = fn.Decrypt(textBoxImportKey.Text, comboBoxUser.Text);
            string tag = fn.getSubStr(0, decapi);
            decapi = decapi.Substring(decapi.IndexOf("|") + 1);
            string encapi = fn.Encrypt(decapi, fn.comname);
            encapi.Replace("'", "''");
            fn.writeUserSql("update corp set code='" + encapi + "',tag='" + tag + "' where name='" + textBoxCorpName.Text + "'");
            MessageBox.Show("Corp API key saved (not checked)");
        }

        private void backgroundWorkerApi_DoWork(object sender, DoWorkEventArgs e)
        {
            timer9m.Enabled = false;
            fetchApiData();
        }

        private void backgroundWorkerApi_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            // display
            comboBoxChar.Enabled = true;
            comboBoxOwner.Enabled = true;
            comboBoxSystem.Enabled = true;
            labelAssets.Text = fn.assetscacheduntil;
            if (fn.apiErrors > 0)
                labelAssets.Text += "  (API error count:" + fn.apiErrors + ")";
            corpid = Convert.ToInt64(fn.readUserSql("select corpID from user where name='" + fn.strToSQLiteStr(charname) + "';"));
            fillFuelGrid(corpid);
            fillProcessGrid(corpid);
            timer9m.Enabled = true;
        }

        private void backgroundWorkerServer_DoWork(object sender, DoWorkEventArgs e)
        {
            timer1m.Enabled = false;
            servervarchange = fn.fetchServerValues(corpid);
        }

        private void backgroundWorkerServer_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (servervarchange)
            {
                corpid = Convert.ToInt64(fn.readUserSql("select corpID from user where name='" + fn.strToSQLiteStr(charname) + "';"));
                fillFuelGrid(corpid);
                fillProcessGrid(corpid);
            }
            timer1m.Enabled = true;
        }

        private void timer9m_Tick(object sender, EventArgs e)
        {
            if (!backgroundWorkerApi.IsBusy)
            {
                comboBoxChar.Enabled = false;
                comboBoxOwner.Enabled = false;
                comboBoxSystem.Enabled = false;
                backgroundWorkerApi.RunWorkerAsync();
            }
        }

        private void timer1m_Tick(object sender, EventArgs e)
        {
            if (!backgroundWorkerServer.IsBusy && !backgroundWorkerApi.IsBusy)
            {
                if(checkBoxMySql.Checked)
                    backgroundWorkerServer.RunWorkerAsync();
            }
        }

        private void dataGridViewFuel_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && e.ColumnIndex == 6)
            {   //change sov status if it doesnt match
                string sov = fn.readSql("select hasSov from apiStarbases where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                if (sov == "True")
                {
                    fn.writeSql("update apiStarbases set hasSov=0 where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (checkBoxMySql.Checked)
                    {
                        if (fn.writeMySql("update tower set hasSov=0 where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid) == 0)
                            fn.writeMySql("replace tower values(" + dataGridViewFuel[1, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewFuel[9, e.RowIndex].Value + "',0,0,'',null)");
                    }
                }
                else
                {
                    fn.writeSql("update apiStarbases set hasSov=1 where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (checkBoxMySql.Checked)
                    {
                        if (fn.writeMySql("update tower set hasSov=1 where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid) == 0)
                            fn.writeMySql("replace tower values(" + dataGridViewFuel[1, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewFuel[9, e.RowIndex].Value + "',1,0,'',null)");
                    }
                }
                fillFuelGrid(corpid);
            }
            if (e.RowIndex >= 0 && e.ColumnIndex == 11)
            {   // set cleared if we refueled
                string cleared = fn.readSql("select cleared from apiStarbases where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                if (cleared == "True")
                {
                    fn.writeSql("update apiStarbases set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (checkBoxMySql.Checked)
                        fn.writeMySql("replace tower values(" + dataGridViewFuel[1, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewFuel[9, e.RowIndex].Value + "'," + dataGridViewFuel[6, e.RowIndex].Value + ",0,'',null)");
                }
                else
                {
                    fn.writeSql("update apiStarbases set cleared=1,clearedBy='" + fn.strToSQLiteStr(charname) + "',clearedTimestamp=datetime('now') where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (checkBoxMySql.Checked)
                        fn.writeMySql("replace tower values(" + dataGridViewFuel[1, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewFuel[9, e.RowIndex].Value + "'," + dataGridViewFuel[6, e.RowIndex].Value + ",1,'" + fn.strToSQLiteStr(charname) + "',UTC_TIMESTAMP())");
                }
                fillFuelGrid(corpid);
            }
        }

        private void dataGridViewFuel_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            string str;
            if (e.ColumnIndex == 9)
            {
                if (dataGridViewFuel[9, e.RowIndex] != null)
                {
                    str = dataGridViewFuel[9, e.RowIndex].Value.ToString();
                    if (str != "")
                    {
                        fn.writeSql("update apiStarbases set groupName='" + fn.strToSQLiteStr(str) + "' where moonName='" + dataGridViewFuel[0, e.RowIndex].Value.ToString() + "'");
                        if (!comboBoxOwner.Items.Contains(str))
                            comboBoxOwner.Items.Add(str);
                        if (checkBoxMySql.Checked)
                        {
                            if (fn.writeMySql("update tower set groupName='" + fn.strToMysqlStr(str) + "' where itemID=" + dataGridViewFuel[1, e.RowIndex].Tag + " and corpID=" + corpid) == 0)
                                fn.writeMySql("replace tower values(" + dataGridViewFuel[1, e.RowIndex].Tag + "," + corpid + ",'" + fn.strToMysqlStr(str) + "'," + dataGridViewFuel[6, e.RowIndex].Value + ",0,'',null)");
                        }
                        return;
                    }
                }
            }
            dataGridViewFuel.CancelEdit();
        }

        private void dataGridViewReaction_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            string str="";
            if (e.ColumnIndex == 9)
            {
                if (dataGridViewReaction[9, e.RowIndex] != null)
                {   // edit remark column
                    if (dataGridViewReaction[9, e.RowIndex].Value!=null)
                        str = dataGridViewReaction[9, e.RowIndex].Value.ToString();
                    /*if (dataGridViewReaction[1, e.RowIndex].Value.ToString().Contains("- Moon "))
                        fn.writeSql("update apiStarbases set remark='" + str + "' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                    else*/
                    if (!dataGridViewReaction[1, e.RowIndex].Value.ToString().Contains("- Moon "))
                    {
                        fn.writeSql("update reactions set remark='" + fn.strToSQLiteStr(str) + "' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                        if (checkBoxMySql.Checked)
                        {
                            if (fn.writeMySql("update silo set remark='" + fn.strToMysqlStr(str) + "' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid) == 0)
                                fn.writeMySql("replace silo values(" + dataGridViewReaction[2, e.RowIndex].Tag + "," + corpid + ",'','" + fn.strToMysqlStr(str) + "',0,'',null)");
                        }
                    }
                    return;
                }
            }
            dataGridViewReaction.CancelEdit();
        }

        private void comboBoxOwner_SelectedIndexChanged(object sender, EventArgs e)
        {
            newTowerFilter();
        }

        private void comboBoxSystem_SelectedIndexChanged(object sender, EventArgs e)
        {
            newTowerFilter();
        }

        private void newTowerFilter()
        {
            // create filterstring
            towerfilter = "";
            groupfilter = "";
            if ((comboBoxOwner.Text != null && comboBoxOwner.Text != "- All -") && (comboBoxSystem.Text != null && comboBoxSystem.Text != "- All -"))
                towerfilter = " and groupName='" + fn.strToSQLiteStr(comboBoxOwner.Text) + "' and moonName like '" + fn.strToSQLiteStr(comboBoxSystem.Text) + "%'";
            else if (comboBoxOwner.Text != null && comboBoxOwner.Text != "- All -")
                towerfilter = " and groupName='" + fn.strToSQLiteStr(comboBoxOwner.Text) + "'";
            else if (comboBoxSystem.Text != null && comboBoxSystem.Text != "- All -")
                towerfilter = " and moonName like '" + fn.strToSQLiteStr(comboBoxSystem.Text) + "%'";
            if (comboBoxOwner.Text != null && comboBoxOwner.Text != "- All -")
                groupfilter = " and groupName='" + fn.strToSQLiteStr(comboBoxOwner.Text) + "'";

            // refresh display
            fillFuelGrid(corpid);
            fillProcessGrid(corpid);
            if (tabControl1.SelectedIndex == 2)
                fillIskGrid();
            /*
            if (tabControl1.SelectedIndex == 3)
                fillAssetsGrid();
            if (tabControl1.SelectedIndex == 4)
                fillPrivAssetsGrid();
            */
            if (tabControl1.SelectedIndex == 5)
                fillFittingGrid();
            if (tabControl1.SelectedIndex == 6)
                fillAmmunitionGrid();
        }

        private void dataGridViewFuel_KeyPress(object sender, KeyPressEventArgs e)
        {
            ResetTimer();
        }

        private void dataGridViewFuel_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            ResetTimer();
        }

        private void dataGridViewFuel_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            ResetTimer();
        }

        private void dataGridViewReaction_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            ResetTimer();
            if (backgroundWorkerApi.IsBusy)
                return;            
            if (e.ColumnIndex == 0)
            {
                if (e.RowIndex >= 0 && dataGridViewReaction[1, e.RowIndex].Value.ToString().Contains("- Moon "))
                {
                    if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag) == closed)
                        fn.writeSql("update apiStarbases set fold='"+opened+"' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag);
                    else if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag) == opened)
                        fn.writeSql("update apiStarbases set fold='"+closed+"' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag);
                    fillProcessGrid(corpid);
                }
                else if (e.RowIndex == -1)
                {
                    fn.writeSql("update apiStarbases set fold='" + dataGridViewReaction.Columns[0].HeaderText + "' where corpID=" + corpid);
                    if (dataGridViewReaction.Columns[0].HeaderText == closed)
                    {
                        dataGridViewReaction.Columns[0].HeaderText = opened;
                        fn.writeSql("update apiStarbases set fold='"+opened+"' where corpID=" + corpid);
                    }
                    else if (dataGridViewReaction.Columns[0].HeaderText == opened)
                    {
                        dataGridViewReaction.Columns[0].HeaderText =closed;
                        fn.writeSql("update apiStarbases set fold='"+closed+"' where corpID=" + corpid);
                    }
                    fillProcessGrid(corpid);
                }
            }
        }

        private void dataGridViewReaction_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            ResetTimer();
        }

        private void dataGridViewReaction_KeyPress(object sender, KeyPressEventArgs e)
        {
            ResetTimer();
        }

        private void dataGridViewFuel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                fillFuelGrid(corpid);
            }
        }

        private void dataGridViewReaction_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                fillProcessGrid(corpid);
            }
        }

        public void ResetTimer()
        {
            if (timer9m.Enabled)
            {
                timer9m.Stop();
                timer9m.Start();
            }
        }

        private void dataGridViewReaction_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && !dataGridViewReaction[1, e.RowIndex].Value.ToString().Contains("- Moon "))
            {
                if (e.ColumnIndex == 8)
                {
                    string dir = fn.readSql("select direction from reactions where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (dir == "") dir = fn.dirli;
                    else if (dir == fn.dirli) dir = fn.dirre;
                    else dir = "";
                    fn.writeSql("update reactions set direction='" + dir + "' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (checkBoxMySql.Checked)
                    {
                        if (fn.writeMySql("update silo set direction='" + dir + "' where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid) == 0)
                            fn.writeMySql("replace silo values(" + dataGridViewReaction[2, e.RowIndex].Tag + "," + corpid + ",'" + dir + "','" + dataGridViewReaction[9, e.RowIndex].Value + "',0,'',null)");
                    }
                    fillProcessGrid(corpid);
                }
                if (e.ColumnIndex == 10)
                {   // set cleared if we refueled
                    string cleared = fn.readSql("select cleared from reactions where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                    if (cleared == "False")
                    {
                        fn.writeSql("update reactions set cleared=1,clearedBy='" + fn.strToSQLiteStr(charname) + "',clearedTimestamp=datetime('now') where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                        if (checkBoxMySql.Checked)
                            fn.writeMySql("replace silo values(" + dataGridViewReaction[2, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewReaction[8, e.RowIndex].Value + "','" + dataGridViewReaction[9, e.RowIndex].Value + "',1,'" + fn.strToSQLiteStr(charname) + "',UTC_TIMESTAMP())");
                    }
                    else if (cleared == "True")
                    {
                        fn.writeSql("update reactions set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dataGridViewReaction[2, e.RowIndex].Tag + " and corpID=" + corpid);
                        if (checkBoxMySql.Checked)
                            fn.writeMySql("replace silo values(" + dataGridViewReaction[2, e.RowIndex].Tag + "," + corpid + ",'" + dataGridViewReaction[8, e.RowIndex].Value + "','" + dataGridViewReaction[9, e.RowIndex].Value + "',0,'',null)");
                    }
                    fillProcessGrid(corpid);
                }
            }
        }

        private void checkBoxMySql_Click(object sender, EventArgs e)
        {
            if (checkBoxMySql.Checked)
            {
                if (textBoxMySql.Text != "")
                {
                    fn.writeUserSql("replace into vars values('useMySql',1,null,null,'" + textBoxMySql.Text + "')");
                    fn.mysqlconnstr = textBoxMySql.Text;
                    textBoxMySql.ReadOnly = true;
                    timer1m.Enabled = true;
                }
                else checkBoxMySql.Checked = false;
            }
            else
            {
                fn.writeUserSql("update vars set int=0 where varName='useMySql'");
                textBoxMySql.ReadOnly= false;
                fn.mysqlconnstr = "";
                timer1m.Enabled = true;
            }
        }

        private void dataGridViewReaction_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex > -1 && dataGridViewReaction.Columns[e.ColumnIndex].CellType.ToString() == "System.Windows.Forms.DataGridViewCheckBoxCell" && dataGridViewReaction.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == null)
            {
                e.PaintBackground(e.ClipBounds, true);
                e.Handled = true;
            }
        }

        private void dataGridViewFuel_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex > -1 && dataGridViewFuel.Columns[e.ColumnIndex].CellType.ToString() == "System.Windows.Forms.DataGridViewCheckBoxCell" && dataGridViewFuel.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == null)
            {
                e.PaintBackground(e.ClipBounds, true);
                e.Handled = true;
            }
        }

        private void buttonDeleteCache_Click(object sender, EventArgs e)
        {
            fn.createTables();
            foreach (FileInfo f in new DirectoryInfo(fn.filepath).GetFiles("*.xml"))
            {
                f.Delete();
            }
            this.Close();
        }

        private string Version()
        {
            return System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!comboBoxChar.Enabled || comboBoxChar.Text == "") return;
            if (tabControl1.SelectedIndex == 2)
                fillIskGrid();
            if (tabControl1.SelectedIndex == 3)
                fillAssetsGrid();
            if (tabControl1.SelectedIndex == 4)
                fillPrivAssetsGrid();
            if (tabControl1.SelectedIndex == 5)
                fillFittingGrid();
            if (tabControl1.SelectedIndex == 6)
                fillAmmunitionGrid();
        }

        private void numericUpDown1_ValueChanged(object sender, EventArgs e)
        {
            fillFuelGrid(corpid);
        }

        private void dataGridViewAmmo_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                if (e.RowIndex >= 0 && dataGridViewAmmo[1, e.RowIndex].Value.ToString().Contains("- Moon "))
                {
                    if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewAmmo[2, e.RowIndex].Tag) == closed)
                        fn.writeSql("update apiStarbases set fold='" + opened + "' where itemID=" + dataGridViewAmmo[2, e.RowIndex].Tag);
                    else if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewAmmo[2, e.RowIndex].Tag) == opened)
                        fn.writeSql("update apiStarbases set fold='" + closed + "' where itemID=" + dataGridViewAmmo[2, e.RowIndex].Tag);
                    fillAmmunitionGrid();
                }
                else if (e.RowIndex == -1)
                {
                    fn.writeSql("update apiStarbases set fold='" + dataGridViewAmmo.Columns[0].HeaderText + "' where corpID=" + corpid);
                    if (dataGridViewAmmo.Columns[0].HeaderText == closed)
                    {
                        dataGridViewAmmo.Columns[0].HeaderText = opened;
                        fn.writeSql("update apiStarbases set fold='" + opened + "' where corpID=" + corpid);
                    }
                    else if (dataGridViewAmmo.Columns[0].HeaderText == opened)
                    {
                        dataGridViewAmmo.Columns[0].HeaderText = closed;
                        fn.writeSql("update apiStarbases set fold='" + closed + "' where corpID=" + corpid);
                    }
                    fillAmmunitionGrid();
                }
            }
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://support.eveonline.com/api/key/createpredefined/134217738");
        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://support.eveonline.com/api/key/createpredefined/17432586/" + fn.readUserSql("select charID from user where name='" + fn.strToSQLiteStr(comboBoxUser.Text) + "'") + "/true");
        }

        private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("https://forums.eveonline.com/default.aspx?g=posts&t=206161&find=unread");
        }

        private void comboBoxUser_TextUpdate(object sender, EventArgs e)
        {
            if (comboBoxUser.Text != "")
            {
                buttonSaveCharKey.Enabled = true;
                buttonSaveCorpKey.Enabled = true;
                buttonDeleteUser.Enabled = true;
            }
            else
            {
                buttonSaveCharKey.Enabled = false;
                buttonSaveCorpKey.Enabled = false;
                buttonDeleteUser.Enabled = false;
            }
        }

        private void textBoxExportName_TextChanged_1(object sender, EventArgs e)
        {
            if (textBoxExportName.Text != ""&&directorRole)
                buttonExportKey.Enabled = true;
            else buttonExportKey.Enabled = false;
        }

        private void buttonTestMySql_Click(object sender, EventArgs e)
        {
            if (textBoxMySql.Text != "")
            {
                fn.mysqlconnstr = textBoxMySql.Text;
                int done = fn.writeMySql("replace into tower values(0,0,'test',0,0,'',null)");
                if (done > 0)
                {
                    MessageBox.Show("seems to work");
                }
            }
        }

        private void checkBoxDebug_CheckedChanged(object sender, EventArgs e)
        {
            fn.debugging = checkBoxDebug.Checked;
            if (checkBoxDebug.Checked)
            {
                if (File.Exists(fn.filepath + "\\debug.txt"))
                    File.Delete(fn.filepath + "\\debug.txt");
            }
        }

        private void textBoxImportKey_TextChanged(object sender, EventArgs e)
        {
            if(textBoxImportKey.Text.Length > 60 && comboBoxUser.Text.Length > 0)
                buttonImportKey.Enabled = true;
        }

        private void dataGridViewIsk_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                if (e.RowIndex >= 0 && dataGridViewIsk[1, e.RowIndex].Value.ToString().Contains("- Moon "))
                {
                    if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewIsk[2, e.RowIndex].Tag) == closed)
                        fn.writeSql("update apiStarbases set fold='" + opened + "' where itemID=" + dataGridViewIsk[2, e.RowIndex].Tag);
                    else if (fn.readSql("select fold from apiStarbases where itemID=" + dataGridViewIsk[2, e.RowIndex].Tag) == opened)
                        fn.writeSql("update apiStarbases set fold='" + closed + "' where itemID=" + dataGridViewIsk[2, e.RowIndex].Tag);
                    fillIskGrid();
                }
                else if (e.RowIndex == -1)
                {
                    fn.writeSql("update apiStarbases set fold='" + dataGridViewIsk.Columns[0].HeaderText + "' where corpID=" + corpid);
                    if (dataGridViewIsk.Columns[0].HeaderText == closed)
                    {
                        dataGridViewIsk.Columns[0].HeaderText = opened;
                        fn.writeSql("update apiStarbases set fold='" + opened + "' where corpID=" + corpid);
                    }
                    else if (dataGridViewIsk.Columns[0].HeaderText == opened)
                    {
                        dataGridViewIsk.Columns[0].HeaderText = closed;
                        fn.writeSql("update apiStarbases set fold='" + closed + "' where corpID=" + corpid);
                    }
                    fillIskGrid();
                }
            }
        }

        private void numericUpDown1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {   // trap annoying keyboard ping
                e.SuppressKeyPress = true;
            }
        }

        void comboBoxChar_MouseWheel(object sender, MouseEventArgs e)
        {
            ((HandledMouseEventArgs)e).Handled = true;
        }

        void comboBoxOwner_MouseWheel(object sender, MouseEventArgs e)
        {
            ((HandledMouseEventArgs)e).Handled = true;
        }

        void comboBoxSystem_MouseWheel(object sender, MouseEventArgs e)
        {
            ((HandledMouseEventArgs)e).Handled = true;
        }
    }
}
